using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace TrainTicket.DAO
{
    public class CustomerDAO
    {
        DbConnection _conn;
        public CustomerDAO()
        {
            _conn= new DbConnection();
        }
        //get all data
        public DataTable GetAllCustomer() 
        {
            return _conn.ExecuteDataTable("sp_GetAllCustomer",null);            
        }

        

        //insert data
        public int InsertCustomerOutput(string customerName, int age, string mobile, string email, string address) 
        {
            SqlParameter[] param= new SqlParameter[6];
            param[0] = new SqlParameter("@CustomerName",SqlDbType.NVarChar,50);
            param[0].Value = customerName;

            param[1] = new SqlParameter("@Age", SqlDbType.Int);
            param[1].Value = age;

            param[2] = new SqlParameter("@Mobile", SqlDbType.NVarChar, 50);
            param[2].Value = mobile;

            param[3] = new SqlParameter("@Email",SqlDbType.NVarChar,50);
            param[3].Value = email;

            param[4] = new SqlParameter("@Address",SqlDbType.NVarChar,200);
            param[4].Value = address;

            param[5] = new SqlParameter();  
            param[5].ParameterName = "@NEWID";  
            param[5].Direction = ParameterDirection.Output;  
            param[5].SqlDbType = SqlDbType.Int;  

            return _conn.GetInsertedIdWithExecuteNonQuery("sp_InsertCustomerOutput", param);
        }

        //insert data
        public int InsertCustomer(string customerName, int age, string mobile, string email, string address) 
        {
            SqlParameter[] param= new SqlParameter[5];
            param[0] = new SqlParameter("@CustomerName",SqlDbType.NVarChar,50);
            param[0].Value = customerName;

            param[1] = new SqlParameter("@Age", SqlDbType.Int);
            param[1].Value = age;

            param[2] = new SqlParameter("@Mobile", SqlDbType.NVarChar, 50);
            param[2].Value = mobile;

            param[3] = new SqlParameter("@Email",SqlDbType.NVarChar,50);
            param[3].Value = email;

            param[4] = new SqlParameter("@Address",SqlDbType.NVarChar,200);
            param[4].Value = address;

            return _conn.ExecuteNonQuery("sp_InsertCustomer",param);
        }
        //check customername
        public int CheckCustomerName(string customerName)
        {
            SqlParameter param = new SqlParameter("@CustomerName", SqlDbType.NVarChar, 50);
            param.Value = customerName;

            return _conn.ExecuteScalar("sp_CheckCustomerName", param);
        }
        //Edit
        public int UpdateCustomer(int customerID,string customerName, int age, string mobile, string email, string address) 
        {
            SqlParameter[] param = new SqlParameter[6];
            param[0] = new SqlParameter("@CustomerID", SqlDbType.Int);
            param[0].Value = customerID;

            param[1] = new SqlParameter("@CustomerName", SqlDbType.NVarChar, 50);
            param[1].Value = customerName;

            param[2] = new SqlParameter("@Age", SqlDbType.Int, 1);
            param[2].Value = age;

            param[3] = new SqlParameter("@Mobile", SqlDbType.NVarChar, 50);
            param[3].Value = mobile;

            param[4] = new SqlParameter("@Email", SqlDbType.NVarChar, 50);
            param[4].Value = email;

            param[5] = new SqlParameter("@Address", SqlDbType.NVarChar, 50);
            param[5].Value = address;
            
            
            return _conn.ExecuteNonQuery("sp_UpdateCustomer", param);
        }
        //GetCustomer By id
        public DataTable GetAllCustomerByID(int customerID) 
        {
            SqlParameter param = new SqlParameter("CustomerID",SqlDbType.Int,1);
            param.Value = customerID;

            return _conn.ExecuteDataTable("sp_GetCustomerByID",param);
        }
        //Delete
        public int DeleteCustomer(int customerID)
        {
            SqlParameter param = new SqlParameter("CustomerID",SqlDbType.Int,1);
            param.Value = customerID;

            return _conn.ExecuteNonQuery("sp_DeleteCustomer",param);
        }
        //Search Customer by CustomerName
        public DataTable GetDataByCustomerName(string customerName)
        {
            SqlParameter param = new SqlParameter("@CustomerName",SqlDbType.NVarChar,50);
            param.Value = customerName;

            return _conn.ExecuteDataTable("sp_GetCustomerByCustomerName",param);
        }
        //Search Customer by Email
        public DataTable GetDataByEmail(string email) 
        {
            SqlParameter param = new SqlParameter("@Email",SqlDbType.NVarChar,100);
            param.Value = email;

            return _conn.ExecuteDataTable("sp_GetCustomerByEmail",param);
        }

        public DataTable GetCustomerByAddress(string address)
        {
            SqlParameter param = new SqlParameter("@Address",SqlDbType.NVarChar,200);
            param.Value = address;

            return _conn.ExecuteDataTable("sp_GetCustomerByAddress",param);
        }

        
    }
}
